<?
if (file_exists(APPPATH.'models/basemodel.php')) include_once(APPPATH.'models/basemodel.php');
else include_once(RESPATH.'models/basemodel.php');

class JobsModel extends BaseModel {
	
	public function JobsModel() {
		parent::BaseModel();
		$this->setModel('job');
		$this->obj =& get_instance();
		$this->load->database();
	}
	
	public function getJobsByDepartment($costcenterid) {
		$sql = "SELECT J.*
				FROM job_costcenter JC INNER JOIN job J ON (JC.jobid = J.id)
				WHERE JC.costcenterid = ".intval($costcenterid)." 
					AND J.cancelled = 0
					AND J.archived = 0
				GROUP BY J.id
				ORDER BY J.job_title";
				
		$rs = $this->db->query($sql)->result();
		
		if (count($rs)) return $rs;
		else return false;
	}
	
	public function getFullJobStatus($archived = 0, $cancelled = 0) {
		$sql = "SELECT J.*, F.friendly_url, C.company_name, count(I.id) as invoices_generated, count(I2.id) as invoices_processed, count(I3.id) as invoices_paid

				FROM job J LEFT JOIN friendly_urls F ON (J.id = F.contentid AND F.content_type = 'job')
					LEFT JOIN company C ON (J.companyid = C.id)
					LEFT JOIN invoice I ON (J.id = I.jobid)
					LEFT JOIN invoice I2 ON (J.id = I2.jobid && I2.processed = 1)
					LEFT JOIN invoice I3 ON (J.id = I3.jobid && I3.paid = 1)
				WHERE 1 = 1 ";
				
		if ($cancelled !== false) $sql .= " AND J.cancelled = ".intval($cancelled)." ";
		if ($archived !== false) $sql .= " AND J.archived = ".intval($archived)." ";
		
		$sql .= " GROUP BY J.id
				ORDER BY C.company_name, J.job_title"; 
		
		$rs = $this->db->query($sql)->result();
		
		if (count($rs)) return $rs;
		else return false;		
	}
	
	public function retrieve_by_name($job_title, $company_name = '') {
		$rs = $this->db->query("SELECT T.*, C.firstname as primary_contact_firstname, C.surname as primary_contact_surname, C2.firstname as secondary_contact_firstname, C2.surname as secondary_contact_surname
				FROM ".$this->db_table." T
					LEFT JOIN contact C ON (T.contactid = C.id)
					LEFT JOIN contact C2 ON (T.contact2id = C.id)
					LEFT JOIN company CO ON (T.companyid = CO.id)
				WHERE T.job_title = ".$this->db->escape($job_title)."
					AND CO.company_name = ".$this->db->escape($company_name)."
					LIMIT 1")->result();

		if (count($rs)) return $rs[0];
		else return false;
	}
	
	public function getAssignedTo($userid, $liveonly = 0){
		$sql = "SELECT C.id as companyid, A.*, C.company_name, J.job_title, J.job_status, C2.centertitle, A2.activity_title, S.usersid as sticky, J.cancelled, J.archived
				FROM assignment A LEFT JOIN job J ON (A.jobid = J.id)
					LEFT JOIN company C ON (J.companyid = C.id)
					LEFT JOIN job_costcenter JC ON (A.job_costcenterid = JC.id)
					LEFT JOIN costcenter C2 ON (JC.costcenterid = C2.id)
					LEFT JOIN activity A2 ON (A.activityid = A2.id)
					LEFT JOIN sticky_jobs S ON (S.jobid = J.id AND S.usersid = A.userid)
				WHERE A.userid = ".intval($userid)." 
						AND C.id IS NOT NULL ";

		if ($liveonly) $sql .= " AND (J.job_status = 'live' OR J.job_status = 'proposed') AND J.archived = 0 AND C.archived = 0 ";
		
		$sql .=	"GROUP BY J.id
				ORDER BY C.company_name, S.usersid ASC";
		
		$rs = $this->db->query($sql)->result();
		
		if (count($rs)) return $rs;
		else return false;
	}
	
	public function getCompanyJobs($companyid, $userid = false, $liveonly = 0){
		$sql = "SELECT C.id as companyid, C.company_name, J.job_title, J.job_status, S.usersid as sticky, J.cancelled, J.archived, J.id as jobid
				FROM job J 
					LEFT JOIN company C ON (J.companyid = C.id)
					LEFT JOIN sticky_jobs S ON (S.jobid = J.id AND S.usersid = ".intval($userid).")
				WHERE 1 = 1 ";
		
		//if ($userid) $sql .= " AND A.userid = ".intval($userid)." ";
		
		$sql .= " AND C.id = ".intval($companyid)." ";
		
		if ($liveonly) $sql .= " AND (J.job_status = 'live' OR J.job_status = 'proposed') AND J.archived = 0 AND C.archived = 0 ";
		
		$sql .=	" GROUP BY J.id
				ORDER BY C.company_name, S.usersid ASC";
		
		$rs = $this->db->query($sql)->result();
		
		if (count($rs)) return $rs;
		else return false;
	}
	
	public function getUpcomingJobs(){
		$sql = "SELECT C.id as companyid, C.company_name, J.job_title, J.job_status, J.cancelled, J.archived, J.id as jobid
				FROM job J 
					LEFT JOIN company C ON (J.companyid = C.id)
				WHERE 1 = 1  AND (J.job_status = 'live' OR J.job_status = 'proposed') AND J.archived = 0 AND C.archived = 0 
				GROUP BY J.id
				ORDER BY C.company_name, J.job_title ASC";
		
		$rs = $this->db->query($sql)->result();
		
		if (count($rs)) return $rs;
		else return false;
	}
	
	public function getJobValues(){
		$sql = "SELECT  J.id AS jobid, J.job_title, J.job_status, SUM( (allocatedhours * hourlyrate) ) AS value, C.id as companyid, C.company_name, MAX(JC.enddate) as estdate
				FROM job J LEFT JOIN job_costcenter JC ON (J.id = JC.jobid)
					INNER JOIN company C ON (C.id = J.companyid)
				WHERE J.cancelled = 0
					AND J.archived = 0
					AND (J.job_status = 'proposed' OR J.job_status = 'live')
					AND JC.deleted = 0
					
				GROUP BY J.id
				HAVING estdate >= NOW()
				ORDER BY J.job_status, estdate
				";
				
		$rs = $this->db->query($sql)->result();

		if (count($rs)) return $rs;
		else return false;
	}
	
	public function getJobValuesByMonth(){
		$sql = "SELECT MONTH(estdate) as tmonth, YEAR(estdate) as tyear, sum(value) as totalvalue, job_status

						FROM (

						SELECT  J.id AS jobid, J.job_title, J.job_status, SUM( (allocatedhours * hourlyrate) ) AS value, C.id as companyid, C.company_name, MAX(JC.enddate) as estdate
						FROM job J LEFT JOIN job_costcenter JC ON (J.id = JC.jobid)
							INNER JOIN company C ON (C.id = J.companyid)
						WHERE J.cancelled = 0
							AND J.archived = 0
							AND (J.job_status = 'proposed' OR J.job_status = 'live')
							AND JC.deleted = 0
						GROUP BY J.id
						ORDER BY J.job_status, estdate

						) X
						
						WHERE estdate IS NOT NULL
							AND estdate >= NOW()
						
						GROUP BY job_status, YEAR(X.estdate), MONTH(X.estdate)

						ORDER BY tyear, tmonth";
				
		$rs = $this->db->query($sql)->result();

		if (count($rs)) return $rs;
		else return false;
	}
	
	public function getAssignedUsers($jobid, $costcenterid = 0, $activityid = 0) {
		$sql = "SELECT U.*, A.jobid, A.job_costcenterid, A.activityid, A2.activity_title, C.centertitle
				FROM assignment A INNER JOIN users U ON (A.userid = U.id)
					LEFT JOIN activity A2 ON (A.activityid = A2.id)
					LEFT JOIN job_costcenter JC ON (A.job_costcenterid = JC.id)
					LEFT JOIN costcenter C ON (JC.costcenterid = C.id)
				WHERE A.jobid = ".intval($jobid);
	
		if ($costcenterid) $sql .= " AND A.job_costcenterid = ".intval($costcenterid);
		if ($activityid) $sql .= " AND A.activityid = ".intval($activityid);
		
		$sql .=	" ORDER BY U.lastname, U.firstname, U.id";

		$rs = $this->db->query($sql)->result();
		
		if (count($rs)) return $rs;
		else return false;
	}
	
	public function getUsersNotAssigned($jobid, $costcenterid = 0, $activityid = 0) {
		
		$sql = "SELECT X.* 
				FROM users X
				WHERE X.id NOT IN (";
		
		$sql .= "SELECT U.id 
				FROM assignment A LEFT JOIN users U ON (A.userid = U.id) 
				WHERE A.jobid = ".intval($jobid);
	
		if ($costcenterid) $sql .= " AND A.job_costcenterid = ".intval($costcenterid);
		if ($activityid) $sql .= " AND A.activityid = ".intval($activityid);
		
		$sql .= " ) ";
		
		$sql .=	" ORDER BY X.lastname";

		$rs = $this->db->query($sql)->result();
		
		if (count($rs)) return $rs;
		else return false;
	}

	public function retrieve_by_pkey($idField) {
		$rs = $this->db->query("SELECT T.*, C.firstname as primary_contact_firstname, C.surname as primary_contact_surname, C2.firstname as secondary_contact_firstname, C2.surname as secondary_contact_surname
				FROM ".$this->db_table." T
					LEFT JOIN contact C ON (T.contactid = C.id)
					LEFT JOIN contact C2 ON (T.contact2id = C.id)
				WHERE T.id = '$idField' LIMIT 1")->result();

		if (count($rs)) return $rs[0];
		else return false;
	}

	public function getNextDeadlineForJob($jobid) {
		$sql = "SELECT D.*
				FROM deadline D
				WHERE D.jobid = ".intval($jobid)."
				ORDER BY D.deadline_duedate
				LIMIT 1";
		
		$rs = $this->db->query($sql)->result();
		
		if (count($rs)) return $rs;
		else return false;
	}
	
	public function getCompletion($jobid) {
		//get all cost centers
		$sql = "SELECT C.centertitle, avg(JC.completion) as cc_average, count(A.id) as act_count, avg(A.completion) as act_average
				FROM job_costcenter JC LEFT JOIN activity A ON (A.job_centerid = JC.id AND A.deleted = 0)
					LEFT JOIN costcenter C ON (C.id = JC.costcenterid)
				WHERE JC.jobid = ".intval($jobid)."
				GROUP BY JC.id";
		
		$rs = $this->db->query($sql)->result();
		
		if (count($rs)) {
			$total = 0;
			foreach ($rs as $costcenter) {
				if ($costcenter->act_count > 0) $total += $costcenter->act_average;
				else $total += $costcenter->cc_average;
			}
			return intval($total / count($rs));
		} else return 0;
	}
	
	public function getAllocatedTimeForJob($jobid) {
		$sql = "SELECT sum(X.total_allocated) as total_allocated
				FROM
		
				(SELECT sum(JC.allocatedhours) as total_allocated
				FROM costcenter C INNER JOIN job_costcenter JC ON (C.id = JC.costcenterid)
				WHERE JC.jobid = ".intval($jobid)." 
					AND JC.deleted = 0
				
				UNION
					
				SELECT sum(A.allocatedhours) as total_allocated
				FROM activity A
				WHERE A.jobid = ".intval($jobid)."
					AND A.deleted = 0) X";
		
		$rs = $this->db->query($sql)->result();
		
		if (count($rs)) return $rs[0]->total_allocated;
		else return 0;
	
	}
	
	public function stick($usersid, $jobid) {
		$this->unstick($usersid, $jobid);
		
		$sql = "INSERT INTO sticky_jobs SET usersid = ".intval($usersid).", jobid = ".intval($jobid);
		
		return $this->db->query($sql);
	}
	
	public function unstick($usersid, $jobid) {
		$sql = "DELETE FROM sticky_jobs WHERE usersid = ".intval($usersid)." AND jobid = ".intval($jobid);
		
		return $this->db->query($sql);
	}
}

?>